
ALTER FUNCTION func_TongDoanhThu
(
	@HopDongID int
	, @fromDate date
	, @toDate date
	
)
RETURNS decimal
WITH encryption
AS
	BEGIN
		DECLARE @T decimal = 
		(		
			select Sum(HD.Doanh_Thu) as Doanh_Thu 
			from DOANHTHU DT 
				INNER JOIN HOPDONG HD ON DT.HopDongID=HD.ID
				INNER JOIN DMTINHTRANG TT ON TT.Ma_DT = HD.Ma_DMTinhTrang
				INNER JOIN DMLOAIVE LV		ON LV.Ma_DT = HD.Ma_DMLoaiVe
				INNER JOIN DMLOAIHOPDONG LHD ON LHD.Ma_DT = HD.Ma_DMLoaiHD 
				INNER JOIN DMCANBO CB ON CB.ID = HD.DMCanBoID
				INNER JOIN DMDONVI DV ON DV.Ma_DT = CB.Ma_DMDonVi
				INNER JOIN DMCHUCVU CV ON CV.Ma_DT = CB.Ma_DMChucVu
			Where DV.isPublic = 1  
				and CV.isPublic = 1
				and CB.isPublic = 1
				and TT.isPublic = 1
				and LV.isPublic = 1
				and LHD.isPublic = 1
				and HD.isPublic = 1 
				and DT.isPublic = 1
				
				and DT.HopDongID = @HopDongID
				and (DT.Ngay between @fromDate and @toDate)
		)
		
		RETURN @T
	END
GO

